﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;
using System.Collections;
using Framework.Library.Core.Config;

namespace Framework.Library.Core.Utils.Database
{
    public enum QueryTypes
	{
		Select,
		Insert,
		Update,
        UpdateCompound,
		Delete,
        TSql
	}

	public enum JoinTypes
	{
		InnerJoin,
		LeftJoin,
		RightJoin,
		OuterJoin
	}

	public enum ConditionOperators
	{
		And,
		Or
	}

    public enum ConditionMath
    {
        Equal,
        NotEqual,
        Greater,
        Less
    }

	public enum SortOrder
	{
		Ascending,
		Descending
	}

	/// <summary>
	/// Class to construct SQL queries
    /// </summary>
    [Serializable]
	public class QueryBuilder
	{
		/// <summary>
		/// Class to track column information
		/// </summary>
        /// 
        [Serializable]
		private class ColumnInfo
		{
			public string Name { get; set; }
			public string Alias { get; set; }
			public string TableName { get; set; }

			public override string ToString()
			{
				StringBuilder builder = new StringBuilder();
				if (!string.IsNullOrEmpty(TableName))
					builder.AppendFormat("{0}.", TableName);
				builder.AppendFormat("{0}", Name);
				if (Alias != null)
					builder.AppendFormat(" AS {0}", Alias);
				return builder.ToString();
			}
		}

		/// <summary>
		/// Class to track table information
        /// </summary>
        [Serializable]
		private class TableInfo
		{
			public string Name { get; set; }
			public JoinTypes JoinType { get; set; }
			public string LeftTable { get; set; }
            public List<string> LeftColumn { get; set; }
            public List<string> RightColumn { get; set; }

			public override string ToString()
			{
				return ToString(true);
			}

			public string ToString(bool isFirstTable)
			{
				// First table has no join
				if (isFirstTable)
					return String.Format("{0}", Name);

				// Table with join
				StringBuilder builder = new StringBuilder();
				switch (JoinType)
				{
					case JoinTypes.InnerJoin:
						builder.Append(" INNER JOIN");
						break;
					case JoinTypes.LeftJoin:
						builder.Append(" LEFT OUTER JOIN");
						break;
					case JoinTypes.RightJoin:
						builder.Append(" RIGHT OUTER JOIN");
						break;
					case JoinTypes.OuterJoin:
						builder.Append(" FULL OUTER JOIN");
						break;
				}
                int comp = LeftColumn.Count;
                if (comp > RightColumn.Count)
                    comp = RightColumn.Count;
                string condition = " {0} ON ";
                for (int i = 0; i < comp; i++)
                {
                    condition += "{1}." + LeftColumn[i] + " = {0}." + RightColumn[i] + " AND ";
                }
                condition = condition.Remove(condition.LastIndexOf("AND") - 1, 5);
                builder.AppendFormat(condition, Name, LeftTable, LeftColumn, RightColumn);
				return builder.ToString();
			}
		}

        /// <summary>
		/// Class to track WHERE condition information
        /// </summary>
        [Serializable]
        protected class ParamterInfo
        {
            public string ParamterText { get; set; }
            public bool ParameterKey { get; set; }
        }
		/// <summary>
		/// Class to track WHERE condition information
        /// </summary>
        [Serializable]
		private class ConditionInfo
		{
			public string Text { get; set; }
            public string Value { get; set; }
            public string TableName { get; set; }
			public ConditionOperators Operator { get; set; }
            public ConditionMath Math { get; set; }

			public override string ToString()
			{
				return ToString(true);
			}

			public string ToString(bool isFirstCondition)
			{
                string math = string.Empty;
                switch (Math)
                {
                    case ConditionMath.Equal:
                        math = "=";
                        break;
                    case ConditionMath.NotEqual:
                        math = "#";
                        break;
                    case ConditionMath.Greater:
                        math = ">";
                        break;
                    case ConditionMath.Less:
                        math = "<";
                        break;
                    default:
                        break;
                }
				// First condition has no logical operator
                if (isFirstCondition)
                {
                    if (!string.IsNullOrEmpty(TableName))
                        return String.Format(" {0}.{1} = " + Configurations.DBPREFIX + "{2}", TableName, Text, Value);
                    return String.Format(" {0} " + math + " " + Configurations.DBPREFIX + "{1}", Text, Value);
                }

				// Condition with logical operator
				StringBuilder builder = new StringBuilder();
				switch (Operator)
				{
					case ConditionOperators.And:
						builder.Append(" AND ");
						break;
					case ConditionOperators.Or:
						builder.Append(" OR ");
						break;
				}
                if (!string.IsNullOrEmpty(TableName))
                    builder.AppendFormat("{0}.", TableName);
                builder.AppendFormat("{0} " + math + " " + Configurations.DBPREFIX + "{1}", Text, Value);
				return builder.ToString();
			}
		}

		/// <summary>
		/// Class to track sort information
        /// </summary>
        [Serializable]
		private class SortInfo
		{
			public string Name { get; set; }
			public string Table { get; set; }
			public SortOrder Direction { get; set; }

			public override string ToString()
			{
				return ToString(true);
			}

			public string ToString(bool isFirstColumn)
			{
				StringBuilder builder = new StringBuilder();
				if (isFirstColumn)
					builder.Append(" ");
				else
					builder.Append(", ");
				if (!String.IsNullOrEmpty(Table))
					builder.AppendFormat("{0}.", Table);
				builder.AppendFormat("{0}", Name);
				if (Direction == SortOrder.Descending)
					builder.Append(" DESC");
				return builder.ToString();
			}
		}

        

        public ArrayList Parameters = new ArrayList();
        private string _Query;
        public string Query
        {
            get
            {
                StringBuilder builder = new StringBuilder();
                switch (QueryType)
                {
                    case QueryTypes.Select:
                        builder.Append("SELECT");
                        builder.AppendFormat(" {0}", BuildColumnString());
                        builder.AppendFormat(" FROM {0}", BuildTableString());
                        builder.Append(BuildConditionString());
                        builder.Append(BuildSortString());
                        break;
                    case QueryTypes.Insert:
                        int index = _nameValuePairs.FindIndex(x=> x.ParameterKey);
                        string returnValue = string.Empty;
                        if(index > -1)
                            returnValue = " SET " + Configurations.DBPREFIX + _nameValuePairs[index].ParamterText + "  = @@IDENTITY";
                        builder.Append("INSERT");
                        builder.AppendFormat(" INTO {0}", BuildTableString(true));
                        builder.AppendFormat(" ({0}) VALUES ({1})" + returnValue,
                        BuildPairNameStrings(_nameValuePairs),
                        BuildPairValueString(_nameValuePairs));

                        break;
                    case QueryTypes.Update:
                        builder.Append("UPDATE");
                        builder.AppendFormat(" {0} SET ", BuildTableString(true));
                        builder.Append(BuildPairNameValueString(_nameValuePairs));
                        builder.Append(BuildConditionString());
                        break;
                    case QueryTypes.UpdateCompound:
                        builder.Append("UPDATE");
                        builder.AppendFormat(" {0} SET ", BuildTableString(true));
                        builder.Append(BuildPairNameValueString(_nameValuePairs, true));
                        builder.Append(BuildConditionString());
                        break;
                    case QueryTypes.Delete:
                        builder.Append("DELETE");
                        builder.AppendFormat(" FROM {0}", BuildTableString(true));
                        builder.Append(BuildConditionString());
                        break;
                    case QueryTypes.TSql:
                        return _Query;
                }
                return builder.ToString();
            }
            set
            {
                _Query = value;
            }
        }

		// Private members to track current settings
		private List<ColumnInfo> _columns = new List<ColumnInfo>();
        private List<ParamterInfo> _nameValuePairs = new List<ParamterInfo>();
		private List<TableInfo> _tables = new List<TableInfo>();
		private List<ConditionInfo> _conditions = new List<ConditionInfo>();
		private List<SortInfo> _sortColumns = new List<SortInfo>();

		// Type of query (SELECT, INSERT, UPDATE, DELETE)
		public QueryTypes QueryType { get; set; }

		/// <summary>
		/// Construction
		/// </summary>
		public QueryBuilder()
		{
			Reset();
		}

        /// <summary>
        /// Construction
        /// </summary>
        public QueryBuilder(string TableName)
        {
            Reset();
            if (_tables.Count > 0)
                throw new Exception("Must supply JOIN parameters in additional tables");
            Table = TableName;
            AddTable(null, null, TableName, null, JoinTypes.InnerJoin);
        }
        public string Table { set; get; }
		/// <summary>
		/// Restores this instance to default settings.
		/// </summary>
		public void Reset()
		{
			QueryType = QueryTypes.Select;
			_columns.Clear();
			_nameValuePairs.Clear();
			_tables.Clear();
		}

		/// <summary>
		/// Adds a column for a SELECT query
		/// </summary>
		/// <param name="colName">Column name</param>
		public void AddColumn(string colName)
		{
			AddColumn(colName, null, null);
		}

		/// <summary>
		/// Adds a column for a SELECT query
		/// </summary>
		/// <param name="colName">Column name</param>
		/// <param name="tableName">Name of table</param>
		public void AddColumn(string colName, string tableName)
		{            
			AddColumn(colName, tableName, null);
		}

		/// <summary>
		/// Adds a column for a SELECT query
		/// </summary>
		/// <param name="colName">Column name</param>
		/// <param name="tableName">Name of table</param>
		/// <param name="alias">Column alias name</param>
		public void AddColumn(string colName, string tableName, string alias)
		{
            if (!string.IsNullOrEmpty(tableName))
                tableName = Configurations.DATABASEOWNER + tableName;
			_columns.Add(new ColumnInfo() { Name = colName, TableName = tableName, Alias = alias });
		}

		/// <summary>
		/// Adds a column name/value pair for INSERT or UPDATE queries
		/// </summary>
		/// <param name="name">Column name</param>
		/// <param name="value">Column value</param>
		public void AddNameValuePair(string name, object value)
		{
            AddNameValuePair(name, value, null, null);
		}

        /// <summary>
        /// Adds a column name/value pair for INSERT or UPDATE queries
        /// </summary>
        /// <param name="name">Column name</param>
        /// <param name="value">Column value</param>
        public void AddNameValuePair(string name, object value, object dbType, object dbDriect)
        {
            bool isKey = (dbDriect != null);
            if(isKey)
                isKey = ((ParameterDirection)dbDriect == ParameterDirection.Output);
            _nameValuePairs.Add(new ParamterInfo() { ParamterText = name, ParameterKey = isKey });
            AddParameter(name, value, dbType, dbDriect);
        }

		/// <summary>
		/// Adds an initial or only table to the query.
		/// </summary>
		/// <param name="tableName"></param>
		public void AddTable(string tableName)
		{
			if (_tables.Count > 0)
				throw new Exception("Must supply JOIN parameters in additional tables");
            AddTable(null, null, tableName, null, JoinTypes.InnerJoin);
		}

		/// <summary>
		/// Adds a subsequent table to the query
		/// </summary>
		/// <param name="leftTable">Name of table on left side of join</param>
		/// <param name="leftColumn">Name of column on left side of join</param>
		/// <param name="newTable">Name of table being added</param>
		/// <param name="rightColumn">Name of column on right side of join</param>
		/// <param name="type">Join type</param>
        public void AddTable(string leftTable, List<string> leftColumn, string newTable, List<string> rightColumn, JoinTypes type)
		{
            newTable = Configurations.DATABASEOWNER + newTable;
            leftTable = Configurations.DATABASEOWNER + leftTable;
			_tables.Add(new TableInfo()
				{
					Name = newTable,
					JoinType = type,
					LeftTable = leftTable,
					LeftColumn = leftColumn,
					RightColumn = rightColumn
				});
		}

		/// <summary>
		/// Adds a WHERE condition to the current query
		/// </summary>
        /// <param name="text">Text of WHERE condition</param>
        /// <param name="value">Value of WHERE condition</param>
		public void AddCondition(string text, object value)
		{
			AddCondition(text, value, null, null, ConditionOperators.And, null, ConditionMath.Equal);
		}
        public void AddCondition(string text, object value, ConditionMath math)
        {
            AddCondition(text, value, null, null, ConditionOperators.And, null, math);
        }

        public void AddCondition(string text, object value, string tableName)
        {
            AddCondition(text, value, null, null, ConditionOperators.And, tableName, ConditionMath.Equal);
        }
        public void AddCondition(string text, object value, string tableName, ConditionMath math)
        {
            AddCondition(text, value, null, null, ConditionOperators.And, tableName, math);
        }

        public void AddCondition(string text, object value, ConditionOperators op)
        {
            AddCondition(text, value, null, null, op, null, ConditionMath.Equal);
        }
        public void AddCondition(string text, object value, ConditionOperators op, ConditionMath math)
        {
            AddCondition(text, value, null, null, op, null, math);
        }

        public void AddCondition(string text, object value, ConditionOperators op, string tableName)
        {
            AddCondition(text, value, null, null, op, tableName, ConditionMath.Equal);
        }
        public void AddCondition(string text, object value, ConditionOperators op, string tableName, ConditionMath math)
        {
            AddCondition(text, value, null, null, op, tableName, math);
        }

        public void AddCondition(string text, object value, object dbType, object dbDriect, string tableName)
        {
            AddCondition(text, value, dbType, dbDriect, ConditionOperators.And, tableName, ConditionMath.Equal);
        }
        public void AddCondition(string text, object value, object dbType, object dbDriect, string tableName, ConditionMath math)
        {
            AddCondition(text, value, dbType, dbDriect, ConditionOperators.And, tableName, math);
        }

        public void AddCondition(string text, object dbValue, object dbType, object dbDirect, ConditionOperators op, string tableName, ConditionMath math)
		{
            if (!string.IsNullOrEmpty(tableName))
                tableName = Configurations.DATABASEOWNER + tableName;
            if (op == ConditionOperators.Or)
            {
                List<ConditionInfo> lst = _conditions.FindAll(l => (l.Text == text));
                if (lst.Count > 0)
                {
                    _conditions.Add(new ConditionInfo() { Text = text, Value = text + lst.Count.ToString(), Operator = op, TableName = tableName, Math = math });
                    AddParameter(text + lst.Count.ToString(), dbValue, dbType, dbDirect);
                }
                return;
            }
            _conditions.Add(new ConditionInfo() { Text = text, Value = text, Operator = op, TableName = tableName, Math = math });
            AddParameter(text, dbValue, dbType, dbDirect);
		}

        public void AddParameter(string text, object value)
        {
            AddParameter(text, value, null, null);
        }

        public void AddParameter(string text, object value, object dbType, object dbDirect)
        {
            Parameters.Add(new object[] { text, value, dbType, dbDirect });
        }

		/// <summary>
		/// Adds an ORDER BY column to the current query
		/// </summary>
		/// <param name="colName">Name of column used for sorting</param>
		public void AddSortColumn(string colName)
		{
			AddSortColumn(colName, SortOrder.Ascending);
		}

		/// <summary>
		/// Adds an ORDER BY column to the current query
		/// </summary>
		/// <param name="colName">Name of column used for sorting</param>
		/// <param name="direction">Sort direction</param>
		public void AddSortColumn(string colName, SortOrder direction)
		{
			AddSortColumn(colName, null, direction);
		}

		/// <summary>
		/// Adds an ORDER BY column to the current query
		/// </summary>
		/// <param name="colName">Name of column used for sorting</param>
		/// <param name="tableName">Name of table this column is part of</param>
		/// <param name="direction">Sort direction</param>
		public void AddSortColumn(string colName, string tableName, SortOrder direction)
		{
            if (!string.IsNullOrEmpty(tableName))
                tableName = Configurations.DATABASEOWNER + tableName;
			_sortColumns.Add(new SortInfo() { Name = colName, Table = tableName, Direction = direction });
		}

		/// <summary>
		/// Constructs a string with list of columns
		/// </summary>
		/// <returns></returns>
		protected string BuildColumnString()
		{
			if (_columns.Count == 0)
				return "*";

			StringBuilder builder = new StringBuilder();
			foreach (ColumnInfo info in _columns)
			{
				if (builder.Length > 0)
					builder.Append(", ");
				builder.Append(info.ToString());
			}
			return builder.ToString();
		}

		/// <summary>
		/// Constructs a string with list of tables
		/// </summary>
		/// <returns></returns>
		protected string BuildTableString()
		{
			return BuildTableString(false);
		}

		/// <summary>
		/// Constructs a string with list of tables
		/// </summary>
		/// <param name="singleTable">Set to true if all but first table should be ignored</param>
		/// <returns></returns>
		protected string BuildTableString(bool singleTable)
		{
			if (_tables.Count == 0)
				throw new Exception("No table specified");

			if (singleTable)
				return _tables[0].ToString(true);

			StringBuilder builder = new StringBuilder();
			foreach (TableInfo info in _tables)
			{
				builder.Append(info.ToString(builder.Length == 0));
			}
			return builder.ToString();
		}


		/// <summary>
		/// Construcuts a string with a list of WHERE conditions
		/// </summary>
		/// <returns></returns>
		protected string BuildConditionString()
		{
			if (_conditions.Count == 0)
				return String.Empty;

			StringBuilder builder = new StringBuilder();
			foreach (ConditionInfo info in _conditions)
			{
				builder.Append(info.ToString(builder.Length == 0));
			}
			builder.Insert(0, " WHERE");
			return builder.ToString();
		}

		/// <summary>
		/// Constructs a string with a list of ORDER BY columns
		/// </summary>
		/// <returns></returns>
		protected string BuildSortString()
		{
			if (_sortColumns.Count == 0)
				return String.Empty;

			StringBuilder builder = new StringBuilder();
			foreach (SortInfo info in _sortColumns)
			{
				builder.Append(info.ToString(builder.Length == 0));
			}
			builder.Insert(0, " ORDER BY");
			return builder.ToString();
		}

		/// <summary>
		/// Constructs a list of names from name/value pairs
		/// </summary>
		/// <param name="pairs">Name/value pairs from which to construct string</param>
		/// <returns></returns>
        protected string BuildPairNameStrings(List<ParamterInfo> pairs)
		{
			StringBuilder builder = new StringBuilder();
			foreach (var pair in pairs)
			{
                if (!pair.ParameterKey)
                {
                    if (builder.Length > 0)
                        builder.Append(", ");
                    builder.AppendFormat("{0}", pair.ParamterText);
                }
			}
			return builder.ToString();
		}

		/// <summary>
		/// Constructs a list of values from name/values pairs
		/// </summary>
		/// <param name="pairs">Name/value pairs from which to construct string</param>
		/// <returns></returns>
        protected string BuildPairValueString(List<ParamterInfo> pairs)
		{
			StringBuilder builder = new StringBuilder();
			foreach (var pair in pairs)
            {
                if (!pair.ParameterKey)
                {
                    if (builder.Length > 0)
                        builder.Append(", ");
                    builder.Append(Configurations.DBPREFIX + pair.ParamterText);
                }
			}
			return builder.ToString();
		}

		/// <summary>
		/// Constructs a list of name/values from name/values pairs
		/// </summary>
		/// <param name="pairs">Name/value pairs from which to construct string</param>
		/// <returns></returns>
        protected string BuildPairNameValueString(List<ParamterInfo> pairs)
        {
            return BuildPairNameValueString(pairs, false);
        }

        protected string BuildPairNameValueString(List<ParamterInfo> pairs, bool Iscompound)
		{
			StringBuilder builder = new StringBuilder();
			foreach (var pair in _nameValuePairs)
			{
                if (!pair.ParameterKey)
                {
                    if (builder.Length > 0)
                        builder.Append(", ");
                    builder.AppendFormat("{0} = " + (Iscompound ? "{0} + " : string.Empty) + Configurations.DBPREFIX + "{0}", pair.ParamterText);
                }
			}
			return builder.ToString();
		}
	}
}
